/*
 * Constant values used throughout the CarShow design.  Some capabilities
 * are parametized here while others are provided via the preferences file
 * interface.  The values herein define the DB configuration as well as the
 * default values for the basic car show classes and awards.  The default
 * server is also defined here, as always it is the local host PC which allows
 * single PC operation to be greatly simplified.
 */
package carshow;

/**
 *
 * @author W Motycka
 */
public class CarShowConstants {
    public static final String CarShowVersion = "0.5";
    /** Name of the club hosting the car show */
    public static final String HostingClubName ="REBELS AUTO CLUB";
    /** default name of the MySQL server in your network, can also be an IP.
     * This is the default, the running server name is kept in the CarShowUI
     * in a global.  This is referenced during init when it tries to connect
     * to the DB to get car classes & clubs for the GUI registration panel.
     * This may change if we start the GUI on the Administration Panel making
     * admin the first task then they are allowed to do Registration operations.
     */
    public static final String defaultServer = "localhost";
    //public static final String defaultServer = "cse.unl.edu"; // use CSE for testing
    //public static final String defaultServer = "192.168.1.11";

    /** base of the URL string used to connect to the SQL DB. */
    static final String DBurlBase = "jdbc:mysql://";
    //public static final String DBport = "8454"; // CarShow port for SQLiteDB
    /** port used by the DB server for connection across TCP/IP network */
    static final String DBport = "3306"; // default MySQL Port
    /** DB name in the SQL server. */
    static final String dbName = "carshow";  // name of the DB in the MySQL server
    /** table name of car show entrants in SQL DB. */
    public static final String winEntrants = "entrants"; // case insensitive version
    public static final String Entrants = "entrants"; // SQL DB table name
    /** table name of car show represented clubs in SQL DB. */
    public static final String winClubs = "clubs";
    public static final String Clubs = "clubs"; // SQL DB table name
    /** table name of the car show judging classes in the SQL DB. */
    public static final String winJudgeClasses = "judgeclasses";
    public static final String JudgeClasses = "judgeclasses"; // SQL DB table name
    /** table name of the car show scores data in the SQL DB. */
    public static final String winScoring = "scoring";
    public static final String Scoring = "scoring"; // SQL DB table name
    /** table name of the special awards table name in the SQL DB. */
    public static final String winSpecials = "specials";
    public static final String Specials = "specials"; // SQL DB table name
    /** table name of the admin panel car show info store in the SQL DB. */
    public static final String winShowData = "showdata";
    public static final String ShowData = "showdata"; // SQL DB table name
    /** table name of the archived past entrants DB - Feature Addition 7/2013*/
    public static final String winArchiveData = "archive";
    public static final String ArchiveData = "archive";
    /** Tunable parameter for Judging tab report */
    public static final int ReportRefreshRate = 3000; // 3000 mSec rate
    // connection timeout value in mSec?
    /** Timeout length to wait for a DB response (15 secs) */
    public static final int ConnectionWaitTime = 1500; // 15 secs?
    /**
     * Driver to use in connecting to the SQL server.  In the future this will
     * involve selecting among the MySQL and MS_SQL drivers and using that
     * value when connecting 
     */
    static final String DBdriver = "com.mysql.jdbc.Driver";
    /** User name within SQL server to access the DB.  This may change over
     * time as we enhance the security making each terminal have its own 
     * user name and making these settable via an admin tool/panel on the
     * server system.
     */
    static final String DBuserName = "csuser";
    /** Password of the above User name for SQL server access */
    static final String DBpassword = "csD3fau1t";
    
    // SQL table initialization statements
    static final String deleteEntrants = "drop table "+dbName+"."+Entrants+";";
    static final String createEntrants = "create table "+dbName+"."+
        Entrants + " (entrant_num integer not null auto_increment primary key, "
        + "fname varchar(45), lname varchar(45), mnemonic varchar(10), "+
        "address varchar(45), "+
        "city varchar(45), state varchar(8), zip varchar(12), "+
        "phone varchar(16), make varchar(45), model varchar(45), "+
        "color varchar(45), year integer, parking_loc varchar(10), "+
        "club varchar(45), preg integer, printed varchar(1), aw1 varchar(30),"+
        " aw2 varchar(30));";
    static final String deleteJudgeClasses = "drop table "+dbName+"."+JudgeClasses+";";
    static final String createJudgeClasses = "create table "+dbName+"."+
        JudgeClasses + " (mnemonic varchar(10) primary key, "+
        "classname varchar(60), StartYear integer, EndYear integer);";
    static final String deleteClubs = "drop table "+dbName+"."+Clubs+";";
    static final String createClubs = "create table "+dbName+"."+
        Clubs + " (club varchar(45) not null primary key);";
    // ANOTHER FEATURE THAT IS YET TO BE IMPLEMENTED, VARIABLE PREFERENCES
    // SETTABLE JUDGING CATEGORIES AND MAX VALUES
    static final String deleteScoring = "drop table "+dbName+"."+Scoring+";";
    static final String createScoring = "create table "+dbName+"."+
        Scoring + " (entrant_num integer primary key, Judge1 varchar(20), "+
        "Judge2 varchar(20), j1_cat1 integer, j1_cat2 integer, "+
        "j1_cat3 integer, j1_cat4 integer, j1_cat5 integer, "+
        "j1_cat6 integer, j1_total integer, j2_cat1 integer, "+
        "j2_cat2 integer, j2_cat3 integer, j2_cat4 integer, "+
        "j2_cat5 integer, j2_cat6 integer, j2_total integer);";
    static final String deleteSpecials = "drop table "+dbName+"."+Specials+";";
    
    static final String createSpecials = "create table "+dbName+"."+
    //    Specials + " (award varchar(30) primary key, entrant_num integer, canshare integer, aorder integer);";
        Specials + " (award varchar(30) primary key, entrant_num integer, canshare integer, aorder integer, canplace integer);";

    static final String createShowData = "create table "+dbName+"."+ShowData+
        " (showname varchar(60), showdate varchar(25), location varchar(20), aux1 varchar(30) );";
    static final String classAddPrepStmt = "insert into "+dbName+"."+JudgeClasses +
        " values (?, ?, ?, ?);";
    static final String awardAddPrepStmt = "insert into "+dbName+"."+Specials+
        " values (?, ?, ?, ?, ?);";
    static final String addHostClub = "insert into "+dbName+"."+Clubs+" (club) values"+
        "(\"Rebels\");"; // "(\""+HostClubName+"\");";
    static final String createArchive = "create table if not exists "+dbName+"."+winArchiveData+
        " (archive_index integer not null auto_increment primary key, "+
        "fname varchar(45), lname varchar(45), "+
        "address varchar(45), city varchar(45), state varchar(8),"+
        " zip varchar(12), phone varchar(16), club varchar(45));";
    // Show Reset SQL strings
    static final String resetEntrants = "TRUNCATE TABLE "+dbName+"."+winEntrants+";";
    static final String resetScores = "TRUNCATE TABLE "+dbName+"."+winScoring+";";
    static final String resetSpecials = "TRUNCATE TABLE "+dbName+"."+Specials+";";
    static final String resetCarClasses = "TRUNCATE TABLE "+dbName+"."+JudgeClasses+";";

    static final String GetAllEntrants = "SELECT fname,lname,address,city,"+
        "state,zip,phone,club FROM "+dbName+"."+winEntrants+" ORDER BY lname asc;";
    static final String GetArchivedEntrants = "SELECT fname,lname,address,"+
        "city,state,zip,phone,club FROM "+dbName+"."+winArchiveData+";";
    
    static final String[] entrantOrder = {"entrant_num","fname","lname",
        "mnemonic","address", "city","state","zip","phone","make","model",
        "color","year","parking_loc","club","preg","printed","aw1","aw2"};
    static final String[] archiveOrder = {"fname","lname","address","city",
        "state","zip","phone"};
    /**
     * Each of these defines the total # of columns each table should have
     * if properly formatted
     */
    static final int entrantFieldCount = 19;
    static final int clubsFieldCount = 1;
    static final int classesFieldCount = 6; // 4 prior to v0.5, 2 field added in 0.5
    static final int archiveFieldCount = 9;
    static final int specialsFieldCount = 6; // 4 prior to v0.5
    static final int showdataFieldCount = 4;
    static final int scoringFieldCount = 17; // THIS WILL CHANGE WHEN WE IMPLEMENT
                                            // XML-SOURCED JUDGING CATEGORIES
                                            // But so will the form & logic...
    
    static final String[][] defaultClassList = {
        {"PRE","Pre Registration Class","1886",""},
        {"A","Street Rods - 1931 & OLDER","1886","1931"},
        {"B","Street Rods - 1932 to 1938","1932","1938"},
        {"C","Street Rods - 1939 to 1948","1939","1948"},
        {"D","Cars Stock - 1931 & OLDER","1886","1931"},
        {"E","Cars Stock - 1932 to 1938","1932","1938"},
        {"F","Cars Stock - 1939 to 1948","1939","1948"},
        {"G","Cars Stock - 1949 to 1960","1949","1960"},
        {"H","Cars Stock - 1961 to 1967","1961","1967"},
        {"I","Cars Stock - 1968 to 1972","1968","1972"},
        {"J","Cars Stock - 1973 to 1989","1973","1989"},
        {"K","Cars Stock - 1990 to Current","1990",""},
        {"L","Cars Modified - 1949 to 1954","1949","1954"},
        {"M","Cars Modified - 1955 to 1957","1955","1957"},
        {"N","Cars Modified - 1958 to 1964","1958","1964"},
        {"O","Cars Modified - 1965 to 1969","1965","1969"},
        {"P","Cars Modified - 1970 to 1973","1970","1973"},
        {"Q","Cars Modified - 1974 to Current","1974",""},
        {"R","Trucks Stock - 1972 & Older","1886","1972"},
        {"S","Trucks Stock - 1973 to Current","1973",""},
        {"T","Trucks Modified - 1959 & Older","1886","1959"},
        {"U","Trucks Modified - 1960 to 1972","1960","1972"},
        {"V","Cars Modified - 1973 to Present","1973",""},
        {"W","Unfinished Car & Truck Primed - All Years","1886",""},
        {"X","Unfinished Car & Truck - All Years","1886",""},
        {"Y","Other - 4X4 All Years","1886",""},
        {"Z","Other - Rat Rods All Years","1886",""},
        {"IM","Imports All Years","1886",""},
        {"YC","Youth (16-20) Domestic Car - All Years","1886",""},
        {"YI","Youth (16-20) - Youth Import All Years","1886",""},
        {"YT","Youth (16-20) - Youth Truck All Years","1886",""}
    };
    // canShare DB value is an integer which will be initially used as a flag
    /** 
     * Special Awards canShare value constant indicating award can 
     * be shared with another award and can be included in class score awards.
     */
    public static final int allowShare = 1;
    /**
     * Special Awards canShare value constant indicating award cannot be 
     * shared with another and award winner is ineligible for class score awards.
     */
    public static final int donotShare = 0;
    /**
     * Special Awards canShare value constant indicating award can be shared
     * but is dropped from class score ranking. (unimplemented, for future use).
     */
    public static final int shareNoRank = 3;
    /**
     * Special Awards canShare value constant indicating award can not be shared
     * but is eligible for class score ranking. (unimplemented, for future use).
     */
    public static final int noShareCanRank = 2;
    // Use a static string and int array pair to represent the default award settings
    static final String[] defaultAwards = {"Best Custom Car","Best Street Rod",
        "Best Truck","Best Interior","Best Paint","Rebels Club Choice",
        "Participants Choice"};
    static final int[] defaultShares = { donotShare, donotShare, donotShare,
        donotShare,donotShare,donotShare,allowShare};
}
